--use PolePosition
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_VendorVisitXMissionStructurePole_SelectForExcel')
	DROP PROCEDURE up_VendorVisitXMissionStructurePole_SelectForExcel
GO

CREATE PROCEDURE up_VendorVisitXMissionStructurePole_SelectForExcel
	 @Vendor_ProjectFK int
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
AS

SET NOCOUNT ON	

SELECT 
	 Pole.[Vendor_Visit_XMissionStructures_PoleId] AS [Visit #]
	,Maps.[Name] AS [Map]
	,Pole.[Section] AS [Section]
	,TopType.[Name] AS [Top Type]
	,NULL AS [D-804]
	,kv.[Name] AS [Kv]
	,Species.[Name] AS [Species]
	,Pole.[Length] AS [Length]
	,Class.[Name] AS [Class]
	,Treatment.[Name] AS [Treatment]
	,Pole.[Year] AS [Year]
	,Pole.[LVisit] AS [Last Visit]
	,Pole.[LVisitor] AS [Last Visitor]
	,Pole.[Latitude] AS [Latitude]
	,Pole.[Longitude] AS [Longitude]	
	,Pole.[Altitude] AS [Altitude]	
	,Vendors.[Name] AS [Vendor]	
	,Contact.[Name] AS [Inspector]	
	,Structure.[Date] AS [Visit Date]
	
FROM tb_Vendor_Visit_XMissionStructures_Poles Pole
INNER JOIN tb_Vendor_Visit_XMissionStructures Structure 
	ON Pole.Vendor_Visit_XMissionStructureFK = Structure.Vendor_Visit_XMissionStructureId
LEFT JOIN tb_Vendor_Contacts Contact
	ON Contact.Vendor_ContactId = Structure.Vendor_ContactFK
LEFT JOIN tb_Vendors Vendors 
	ON Vendors.VendorId = Contact.VendorFK
LEFT JOIN tb_Vendor_Visit_Type kv 
	ON kv.Vendor_Visit_TypeId = Structure.Vendor_Visit_TypeFK
LEFT JOIN tb_XMissionStructures_PoleTopTypes TopType
	ON TopType.XMissionStructures_PoleTopTypeId = Pole.XMissionStructures_PoleTopTypeFk
LEFT JOIN tb_XMissionStructures_PoleSpecies Species
	ON Species.XMissionStructures_PoleSpeciesId = Pole.XMissionStructures_PoleSpeciesFk
LEFT JOIN tb_XMissionStructures_PoleTreatments Treatment
	ON Treatment.XMissionStructures_PoleTreatmentId = Pole.XMissionStructures_PoleTreatmentFk
LEFT JOIN tb_XMissionStructures_PoleClasses Class
	ON Class.XMissionStructures_PoleClassId = Pole.XMissionStructures_PoleClassFk
LEFT JOIN tb_Utility_Polygons Maps
	ON Maps.Utility_PolygonId = Pole.[MapFk]	
WHERE
	Structure.Vendor_ProjectFK = @Vendor_ProjectFK


GO
 